/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.gis.repository;

import me.zhengjie.modules.gis.domain.FeedHistory;
import me.zhengjie.modules.gis.service.dto.FeedHistoryDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.Map;

/**
* @website https://el-admin.vip
* @author ZhuangBing Yan  https://blog.csdn.net/fegus/article/details/124878389
* @date 2022-08-15
**/
public interface FeedHistoryRepository extends JpaRepository<FeedHistory, Long>, JpaSpecificationExecutor<FeedHistory> {



    List<FeedHistory> findAllByPositionStartLatIsNull();

    List<FeedHistory> findAllByPositionStartLatIsNotNullAndLinePositionIsNull();

    /**
     * 李淳风:
     * 118.6460912987,32.4391594145
     * 118.5362958667,30.4347759953
     * 122.4557392352,32.3576164176
     * 122.3427282838,30.4362079172
     * @param enterInTime
     * @param pageable
     * @return
     */
    @Query(value="  select * from gis_feed_history " +
            "where position_start_lng >= 119 AND position_start_lng <= 122.5 AND position_start_lat >= 30.4 AND position_start_lat <= 32.4 " +
            "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null ",
            countQuery = "select count(1) from gis_feed_history " +
                    "where position_start_lng >= 119 AND position_start_lng <= 122.5 AND position_start_lat >= 30.4 AND position_start_lat <= 32.4 " +
                    "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null",
            nativeQuery = true
    )
    Page<FeedHistory>   getByDateForPage(@Param("enterInTime")String enterInTime, Pageable pageable);

    @Query(value="  select enter_in_time as enterInTime,line_position as linePosition    from gis_feed_history " +
            "where position_start_lng >= 119.9 AND position_start_lng <= 121 AND position_start_lat >= 30.7 AND position_start_lat <= 31.5 " +
            "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null ",
            countQuery = "select count(1) from gis_feed_history " +
                    "where position_start_lng >= 119.9 AND position_start_lng <= 121 AND position_start_lat >= 30.7 AND position_start_lat <= 31.5 " +
                    "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null ",
            nativeQuery = true
    )
    Page<Map<String,Object>> getLineDataByDate(String enterInTime, Pageable pageable);
    @Query(value="  select * from gis_feed_history " +
            "where position_start_lng >= 119.9 AND position_start_lng <= 121 AND position_start_lat >= 30.7 AND position_start_lat <= 31.5 " +
            "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null ",
            nativeQuery = true
    )
    List<FeedHistory> getByDateFor(String enterInTime);
    @Query(value="  select sum(net_weight) as netWeight,position_start_lng as startLng,position_start_lat as startLat   from gis_feed_history " +
            "where position_start_lng >= 119.9 AND position_start_lng <= 121 AND position_start_lat >= 30.7 AND position_start_lat <= 31.5 " +
            "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null group by position_start_lng,position_start_lat ",
            nativeQuery = true
    )
    List<Map<String,Object>> findStartPoint(String enterInTime);
    @Query(value="  select count(DISTINCT concat(position_start_lat,position_start_lng)) as num , sum(net_weight) as weight ,area as area  from gis_feed_history " +
            "where position_start_lng >= 119.9 AND position_start_lng <= 121 AND position_start_lat >= 30.7 AND position_start_lat <= 31.5 " +
            "AND DATE_FORMAT(enter_in_time,'%Y-%m') = :enterInTime AND line_position is not null group by area order by weight desc ",
            nativeQuery = true
    )
    List<Map<String, Object>> findArea(String enterInTime);
}